<?

//Data loading
$title='REKAP UMUR HUTANG';
if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
else $perush = 'PT. MASAJI TATANAN CONTAINER';

$page   = 'PAGE';
$DEFINE_FONT=0;
$Result = GridList ();
if ($Result) {
	foreach ($Result as $Row) {
		foreach ($Row as $Field) {
			if (strlen($strExcelRow)) $strExcelRow .= ";$Field";
			else $strExcelRow = "$Field";
		}
		$strOutput .= $strExcelRow."\n";
		$strExcelRow="";
	}
	header("Content-type:application/vnd.notepad");
	header("Content-Disposition:filename=ais_aging_hutang".".csv");
	print $strOutput;
}

function GetFromDate () {
// 	list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));
 	return array($Day,$Month,$Year,$NMonth);
}

function GetLastPeriod ($Month, $Year) {
	list($LDay,$LMonth,$LYear,$LNMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,$Month,0,$Year)));
	return array($LMonth,$LYear,$LNMonth);
}


function FlushCustomer ($TableName, $tmp) {
	global $DBConnection,$DBName,$DBGeneral;

	if ($tmp) $TEMPORARY = "TEMPORARY";
        
	//$SQL = "DROP TABLE IF EXISTS `$TableName`;";
	//$DBConnection->dbc->query($SQL);
	$SQL= "CREATE $TEMPORARY TABLE `$TableName` (
			  `cucode` varchar(20) NOT NULL default '',
			  `cunama` varchar(50) default NULL,
			  `top` int(4) default NULL

			  )";
	$DBConnection->dbc->query($SQL);
    $SQL = "INSERT INTO $DBName.`$TableName` SELECT cucode,cuname,top FROM tbldebtur ";
    $DBConnection->dbc->query($SQL);
        
	return true;
}

function GridList () {
		 	global $DBConnection;
			$TEMP = "TEMPORARY";

		 	//if (!FlushCustomer ("tbldebtur", 1)) return false;
			//$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));

			list($day,$month,$year) = explode("/",GetParam("agingDate",""));
			$current_date = "$year-$month-$day";
			$LastDate  = GetLastDateReport();

			//$SQL = "DROP TABLE IF EXISTS `ais_hutang_terhutang`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_terhutang` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,	
                                          `nobkta` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `jumlah` decimal(15,0) default NULL,
					  `cr_term` tinyint(4)default NULL,
                                          `top_curr` date,
                                          `top_30` date,
                                          `top_60` date,
                                          `aging` varchar(16)
					);
				   ";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					INSERT INTO ais_hutang_terhutang
					SELECT KTRASL, TGLBKT, NOBKT,NOBKTA, tbldebtur.cucode, tbldebtur.cuname, URAIAN, CURR, SUM(JUMLAH), TOP, 

					ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ) as top_curr,
					ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ) as top_30,
					ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ) as top_60,

                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ),'0',
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ),'30',
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ),'60','60g')))

					FROM ais_hutang
					LEFT JOIN tbldebtur ON ais_hutang.DEBTUR=tbldebtur.cucode
					WHERE srt='1' AND ais_hutang.tglbkt<='$current_date'
				   ";

			if (strlen($LastDate))
			$SQL .= "AND ais_hutang.tglbkt>'$LastDate' ";
			
			if (GetParam("fromSkada","")) 
			$SQL .= "AND ais_hutang.skada >= '".GetParam("fromSkada","")."' ";

			if (GetParam("curr","")) 
			$SQL .= "AND ais_hutang.curr = '".GetParam("curr","")."' ";

			if (GetParam("toSkada",""))
			$SQL .= "AND ais_hutang.skada <= '".GetParam("toSkada","")."' ";

			if (GetParam("kdtype","")) 
			$SQL .= "AND tbldebtur.kdtype  = '".GetParam("kdtype","")."' ";

			if (GetParam("office",""))  $SQL .= "AND ais_hutang.ktrasl = '".GetParam("office","")."' ";
                        $SQL .= " GROUP BY  nobkt,skada  ";
			$SQL .= "order by nobkt ";
                        
                        //print $SQL;die();
			$DBConnection->dbc->query($SQL);

			//$SQL = "DROP TABLE IF EXISTS `ais_hutang_terbayar`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_terbayar` (
					  `nobkt` varchar(16) default NULL,					  
					  `nobkta` varchar(16) default NULL,
                      `bayar` decimal(15,0) default NULL,
					  `debtur` varchar(64) default NULL,					  
					  `debnam` varchar(128) default NULL                                          
					) ;
				   ";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					INSERT INTO ais_hutang_terbayar
					SELECT NOBKT, NOBKTA, SUM(JUMLAH),debtur,debnam
					FROM ais_hutang
                                        LEFT JOIN tbldebtur ON ais_hutang.DEBTUR=tbldebtur.cucode
					WHERE srt='2' AND ais_hutang.tglbkt<='$current_date' ";

			if (strlen($LastDate))
			$SQL .= "AND ais_hutang.tglbkt>'$LastDate' ";

                        //print $SQL;die();
			if (GetParam("office",""))  
                        $SQL .= "AND ais_hutang.ktrasl = '".GetParam("office","")."' ";

			if (GetParam("curr","")) 
			$SQL .= "AND ais_hutang.curr = '".GetParam("curr","")."' ";

                        $SQL .= "group by nobkta, debtur order by nobkta";
                        //print $SQL;die();
			$DBConnection->dbc->query($SQL);
			//$DBConnection->dbc->debug();

//Auto Update Using Cache Begin Here
			 if (strlen($LastDate)) {

				//$SQL = "DROP TABLE IF EXISTS `ais_hutang_sisa_period`";
				//$DBConnection->dbc->query($SQL);

				$SQL = "
						CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_sisa_period` (
						  `ktrasl` varchar(5) default NULL,
						  `tglbkt` date,
						  `aging_date` date,					  
						  `nobkt` varchar(16) default NULL,
						  `debtur` varchar(16) default NULL,
						  `debnam` varchar(32) default NULL,
						  `uraian` text default NULL,
                          `cr_term` tinyint(4)default NULL,
						  `jumlah` decimal(15,0) default NULL,
                          `aging` varchar(16),
                          `fromskada` varchar(16),
                          `toskada` varchar(16),
                          `curr`varchar(5)
						)
					   ";
				$DBConnection->dbc->query($SQL);
	             $SQL = "
	                      INSERT INTO ais_hutang_sisa_period
	                      SELECT *
	                      FROM ais_hutang_sisa
	                      WHERE aging_date='$LastDate' AND fromskada='".GetParam("fromSkada","")."' AND toskada='".GetParam("toSkada","")."'
						";
                              if (GetParam("curr",""))  $SQL .= " AND curr='".GetParam("curr","")."' ";
                              
				 $DBConnection->dbc->query($SQL);

	             $SQL = " 
	             		  INSERT INTO ais_hutang_sisa_period
	                      SELECT ktrasl,tglbkt,'$current_date',nobkt,debtur,debnam,uraian,cr_term,jumlah,aging,'".GetParam("fromSkada","")."','".GetParam("toSkada","")."','".GetParam("curr","")."'
	                      FROM ais_hutang_terhutang
	                    ";
				 $DBConnection->dbc->query($SQL);

				 if (isTimeout(45,$current_date,$LastDate)) {
		             $SQL = "
		                      INSERT INTO ais_hutang_sisa
		                      SELECT 
		                      ais_hutang_sisa_period.ktrasl,
		                      ais_hutang_sisa_period.tglbkt,
		                      '$current_date',
		                      ais_hutang_sisa_period.nobkt,
		                      ais_hutang_sisa_period.debtur,
		                      ais_hutang_sisa_period.debnam,
		                      ais_hutang_sisa_period.uraian,
		                      ifnull(ais_hutang_sisa_period.cr_term, 0) AS top,
		                      ifnull(ais_hutang_sisa_period.jumlah,0)-ifnull(ais_hutang_terbayar.bayar,0),

			                  IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_hutang_sisa_period.cr_term DAY ),'0',
			                  IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_hutang_sisa_period.cr_term DAY ),'30',
			                  IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_hutang_sisa_period.cr_term DAY ),'60','60g'))),
                                          '".GetParam("fromSkada","")."','".GetParam("toSkada","")."','".GetParam("curr","")."'

		                      FROM
		                      ais_hutang_sisa_period
		                      LEFT JOIN ais_hutang_terbayar ON (ais_hutang_sisa_period.nobkt = ais_hutang_terbayar.nobkta AND ais_hutang_sisa_period.debtur=ais_hutang_terbayar.debtur)
		                      WHERE (ifnull(ais_hutang_terbayar.nobkt, '') = '') OR ifnull(ais_hutang_sisa_period.jumlah-ais_hutang_terbayar.bayar,0) <> 0
		                      ORDER BY debtur,nobkt
		                    ";
					 $DBConnection->dbc->query($SQL);
			 	} else {
					//$SQL = "DROP TABLE IF EXISTS `ais_hutang_sisa_notimeout`";
					//$DBConnection->dbc->query($SQL);
					//$DBConnection->dbc->debug();					
		             $SQL = "				 	
						CREATE $TEMP TABLE `ais_hutang_sisa_notimeout` (
                        `ktrasl` varchar(5) default NULL,
                        `tglbkt` date default NULL,
                        `aging_date` date default NULL,
                        `nobkt` varchar(16) default NULL,
                        `debtur` varchar(16) default NULL,
                        `debnam` varchar(32) default NULL,
                        `uraian` text,
                        `cr_term` tinyint(4) default NULL,
                        `jumlah` decimal(15,0) default NULL,
                        `aging` varchar(16) default NULL,
                        `fromskada` varchar(64) default NULL,
                        `toskada` varchar(64) default NULL,
                        `curr` varchar(6) NOT NULL default ''						)
					";
					$DBConnection->dbc->query($SQL);
					//$DBConnection->dbc->debug();

		             $SQL = "
		                      INSERT INTO ais_hutang_sisa_notimeout
		                      SELECT 
		                      ais_hutang_sisa_period.ktrasl,
		                      ais_hutang_sisa_period.tglbkt,
		                      '$current_date',
		                      ais_hutang_sisa_period.nobkt,
		                      ais_hutang_sisa_period.debtur,
		                      ais_hutang_sisa_period.debnam,
		                      ais_hutang_sisa_period.uraian,
		                      ifnull(ais_hutang_sisa_period.cr_term, 0) AS top,
		                      ifnull(ais_hutang_sisa_period.jumlah,0)-ifnull(ais_hutang_terbayar.bayar,0),

			                  IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_hutang_sisa_period.cr_term DAY ),'0',
			                  IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_hutang_sisa_period.cr_term DAY ),'30',
			                  IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_hutang_sisa_period.cr_term DAY ),'60','60g'))),
                              '".GetParam("fromSkada","")."','".GetParam("toSkada","")."','".GetParam("curr","")."'
		                      FROM
		                      ais_hutang_sisa_period
		                      LEFT JOIN ais_hutang_terbayar ON (ais_hutang_sisa_period.nobkt = ais_hutang_terbayar.nobkta AND ais_hutang_sisa_period.debtur=ais_hutang_terbayar.debtur)
		                      WHERE (ifnull(ais_hutang_terbayar.nobkt, '') = '') OR ifnull(ais_hutang_sisa_period.jumlah,0)-ifnull(ais_hutang_terbayar.bayar,0) <> 0
		                      ORDER BY debtur,nobkt
		                    ";
					 $DBConnection->dbc->query($SQL);
					 

				}

			 } else {
		             $SQL = "
		                      INSERT INTO ais_hutang_sisa
		                      SELECT 
		                      ais_hutang_terhutang.ktrasl,
		                      ais_hutang_terhutang.tglbkt,
		                      '$current_date',
		                      ais_hutang_terhutang.nobkt,
		                      ais_hutang_terhutang.debtur,
		                      ais_hutang_terhutang.debnam,
		                      ais_hutang_terhutang.uraian,
		                      ifnull(ais_hutang_terhutang.cr_term, 0) AS top,
		                      ifnull(ais_hutang_terhutang.jumlah,0)-ifnull(ais_hutang_terbayar.bayar,0),
		                      
		                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_hutang_terhutang.cr_term DAY ),'0',
		                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_hutang_terhutang.cr_term DAY ),'30',
		                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_hutang_terhutang.cr_term DAY ),'60','60g'))),
                                          '".GetParam("fromSkada","")."','".GetParam("toSkada","")."','".GetParam("curr","")."'
                                          
		                      FROM
		                      ais_hutang_terhutang
		                      LEFT JOIN ais_hutang_terbayar ON (ais_hutang_terhutang.nobkt = ais_hutang_terbayar.nobkta AND ais_hutang_terhutang.debtur=ais_hutang_terbayar.debtur)
		                      WHERE (ifnull(ais_hutang_terbayar.nobkt, '') = '') OR ifnull(ais_hutang_terhutang.jumlah-ais_hutang_terbayar.bayar,0) <> 0
		                      ORDER BY debtur,nobkt
		                    ";
					$DBConnection->dbc->query($SQL);

			}
//Auto Update Using Cache End Here

//			$SQL = "SELECT * FROM ais_aging WHERE aging_date='$current_date'";
			if (isTimeout(45,$current_date,$LastDate)) 
				$TableName = "ais_hutang_sisa";
			else
				$TableName = "ais_hutang_sisa_notimeout";

			$SQL = "
				SELECT ktrasl, nobkt, debtur, debnam, uraian, cr_term as top, 
				SUM(if($TableName.aging='0',jumlah,0)) AS jumlahcurr, 
                                SUM(if($TableName.aging='30',jumlah,0)) AS jumlah30, 
                                SUM(if($TableName.aging='60',jumlah,0)) AS jumlah60, 
                                SUM(if($TableName.aging='60g',jumlah,0)) AS jumlah60g
				FROM $TableName 
				WHERE aging_date='$current_date' AND 
                                
                                fromskada='".GetParam("fromSkada","")."' AND 
                                toskada='".GetParam("toSkada","")."' AND 
                                curr='".GetParam("curr","")."'
                                
				GROUP BY debtur
				ORDER BY debtur
			";
//print $SQL;
			return $DBConnection->dbc->get_results($SQL, ARRAY_A);
}

function isTimeout($day,$current_date,$LastDate) {
	global $DBConnection;
	if (!$LastDate) return 1;
	$SQL = "SELECT IF(ADDDATE('$LastDate', INTERVAL $day DAY)<='$current_date',1,0)";
	//print $SQL;die();
	return $DBConnection->dbc->get_var($SQL);
}

function ReadCOA($no) {
		    $DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
 			$SQL = "SELECT namacc FROM tblcoa WHERE skada='".$no."'";
			$MyResult = $DBConnection->dbc->get_var($SQL);
			return $MyResult;
}

function GetLastDateReport() {
	global $DBConnection;
	list($day,$month,$year) = explode("/",GetParam("agingDate",""));
	$current_date = "$year-$month-$day";

    $SQL = "DELETE FROM ais_hutang_sisa WHERE aging_date>='$current_date' ";
    if (GetParam("curr","")) $SQL .= " AND curr='".GetParam("curr","")."' ";
	if (GetParam("fromSkada","")) $SQL .= " AND fromskada = '".GetParam("fromSkada","")."' ";
    if (GetParam("toSkada","")) $SQL .= " AND toskada = '".GetParam("toSkada","")."' ";                
    $DBConnection->dbc->query($SQL);

	$SQL = "SELECT max(aging_date) FROM ais_hutang_sisa WHERE aging_date<='$current_date' ";
    if (GetParam("curr","")) $SQL .= " AND curr='".GetParam("curr","")."' ";
	if (GetParam("fromSkada","")) $SQL .= " AND fromskada = '".GetParam("fromSkada","")."' ";
    if (GetParam("toSkada","")) $SQL .= " AND toskada = '".GetParam("toSkada","")."' ";        

	return $DBConnection->dbc->get_var($SQL);
}

class PDFReport extends PDF {

	function Header() {
		global $title,$perush,$page,$DEFINE_FONT;
		if ($DEFINE_FONT==0) {
			$this->AddFont('Arial','','arialn.php');
			$DEFINE_FONT=1;
		}

		list($Day,$Month,$Year,$NMonth) = GetFromDate();
		list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month, $Year);

		$this->SetFont('Arial','',10);
    	$this->Cell(260,5,$perush,0,1,'C',0);
    	$this->Cell(260,5,$title,0,1,'C',0);    	
    	$this->Cell(260,5,"AS OF: ".GetParam("agingDate",""),0,1,'C',0);
/*    	
    	if (GetParam("fromSkada",""))
    	   $this->Cell(260,5,"FROM SKADA: ".GetParam("fromSkada","")." - ".ReadCOA(GetParam("fromSkada","")),0,1,'L',0);
    	else
    	   $this->Cell(260,5,"FROM SKADA: ALL",0,1,'L',0);
    	if (GetParam("toSkada",""))
    	   $this->Cell(260,5,"TO SKADA: ".GetParam("toSkada","")." - ".ReadCOA(GetParam("toSkada","")),0,1,'L',0);
    	else   	
    	$this->Cell(260,5,"TO SKADA: ALL",0,1,'L',0);
*/        

      $this->Cell(260,5,"SKADA: ".GetParam("fromSkada","")." - ".ReadCOA(GetParam("fromSkada","")),0,1,'L',0);
    	$this->Cell(130,5,"CURRENCY: ".GetParam("curr",""),0,0,'L',0); 
    	$this->Cell(130,5,$page." ".$this->PageNo(),0,1,'R',0);
    	$this->Line(10,40,270,40);
    	$this->Ln();
    	$this->Ln();
	    //Header

	    //Column widths
	    $w=array(20,80,10,30,30,30,30,30);
	    $this->SetFont('Arial','',8);
	    $this->Cell($w[0],5,"KTRASL",1,0,'C',0);
	    $this->Cell($w[1],5,"DEBTUR",1,0,'C',0);
	    $this->Cell($w[2],5,"CR",1,0,'C',0);
		$this->Cell($w[3],5,"CURRENT",1,0,'C',0);
		$this->Cell($w[4],5,"1-30",1,0,'C',0);
		$this->Cell($w[5],5,"31-60",1,0,'C',0);		
		$this->Cell($w[6],5,">60",1,0,'C',0);
		$this->Cell($w[7],5,"TOTAL",1,0,'C',0);
	    $this->Ln();

	}

	function GridTable($MyResult) {
	    //Column widths
	    $w=array(20,80,10,30,30,30,30,30);

	    //Data
	    foreach($MyResult as $row)
	    {
		    $row_total  = $row["jumlahcurr"]+$row["jumlah30"]+$row["jumlah60"]+$row["jumlah60g"];
			$this->Cell($w[0],5,GetParam("office",""),'LR');
	        $this->Cell($w[1],5,$row["debtur"].' - '.$row["debnam"],'LR');
	        $this->Cell($w[2],5,$row["top"],'LR',0,'R');
			$this->Cell($w[3],5,number_format($row["jumlahcurr"],2),'LR',0,'R');
	        $this->Cell($w[4],5,number_format($row["jumlah30"],2),'LR',0,'R');
			$this->Cell($w[5],5,number_format($row["jumlah60"],2),'LR',0,'R');
			$this->Cell($w[6],5,number_format($row["jumlah60g"],2),'LR',0,'R');
			$this->Cell($w[7],5,number_format($row_total,2),'LR',0,'R');
	        $this->Ln();
	        
		    $total_cur  = $total_cur + $row["jumlahcurr"];
		    $total_30   = $total_30 + $row["jumlah30"];
		    $total_60   = $total_60 + $row["jumlah60"];
		    $total_60g  = $total_60g + $row["jumlah60g"];
	    }
	    $total_row  = $total_cur + $total_30 + $total_60 + $total_60g;		
	    $this->Cell($w[0]+$w[1]+$w[2],5,"TOTAL:",1,0,'R',0);
	    $this->Cell($w[3],5,number_format($total_cur,2),1,0,'R',0);
	    $this->Cell($w[4],5,number_format($total_30,2),1,0,'R',0);
	    $this->Cell($w[5],5,number_format($total_60,2),1,0,'R',0);
		$this->Cell($w[6],5,number_format($total_60g,2),1,0,'R',0);
		$this->Cell($w[7],5,number_format($total_row,2),1,0,'R',0);
	    $this->Ln();

	    //Closure line
	}

	function Footer() {
	    //Closure line
	    $this->Cell(260,0,'','T');
	}

}


?>
